//结构
var express = require('express');
//引入mysql模块
var mysql = require('mysql');
//引入数据库配置模块
var dbconfig = require('./dbconfig');
//路由
var router = express.Router();

//select-get
//delete-delete
//updata-put
//insert-post

//分页查询学生请求
router.get('/selectUsers', function(req, res, next) {
    //获取参数
    let {pageNo,pageSize,yhm} = req.query;
    //首条记录下标
    let pageIndex = (pageNo-1)*pageSize;
    //创建数据库连接池
    var pool = mysql.createPool(dbconfig.config);
    //创建数据库连接
    pool.getConnection((err,conn)=>{
        if(err){
            console.log('连接失败！');
            return;
        }
        //定义sql
        let sql = "SELECT userCode, "
                  + "     userName, "
                  + "  CASE "
                  + "     WHEN gender = 1 THEN '男' "
                  + "     WHEN gender = 2 THEN '女' "
                  + "     ELSE '未知' "
                  + "  END AS sex, "
                  + "  CEIL(DATEDIFF(NOW(),birthday)/365) AS age, "
                  + "  phone, "
                  + "  roleName "
                  + " FROM smbms_user AS u,smbms_role AS r "
                  + " WHERE u.userRole = r.id "
                  + " AND userName LIKE '%" + yhm + "%' "
                  + " LIMIT " + pageIndex + ", " + pageSize;
        console.log(sql);
        //执行SQL业务
        conn.query(sql,(err,data)=>{
            if(err){
                console.log('sql执行失败！');
                return;
            }
            console.log(data);
            res.send({"usersData":data});
        })
        //释放连接
        conn.release();
    })

});

//查询总页数
router.get('/getMaxPage', function(req, res, next) {
    //获取参数
    let {pageSize,yhm} = req.query;
    //创建数据库连接池
    var pool = mysql.createPool(dbconfig.config);
    //创建数据库连接
    pool.getConnection((err,conn)=>{
        if(err){
            console.log('连接失败！');
            return;
        }
        //定义sql
        let sql = "SELECT COUNT(1) AS rs FROM smbms_user WHERE userName LIKE '%" + yhm + "%'";
        console.log(sql);
        //执行SQL业务
        conn.query(sql,(err,data)=>{
            if(err){
                console.log('sql执行失败！');
                return;
            }
            //计算总页数
            var maxPage = data[0].rs % pageSize == 0 ? data[0].rs / pageSize : parseInt(data[0].rs / pageSize) + 1;
            res.send({"maxPage":maxPage});
        })
        //释放连接
        conn.release();
    })

});

//删除用户信息
router.delete('/deleteUser', function(req, res, next) {
    //获取参数
    let {userCode} = req.query;
    //创建数据库连接池
    var pool = mysql.createPool(dbconfig.config);
    //创建数据库连接
    pool.getConnection((err,conn)=>{
        if(err){
            console.log('连接失败！');
            return;
        }
        //定义sql
        let sql = "DELETE FROM smbms_user WHERE userCode = ?";
        console.log(sql);
        //执行SQL业务
        conn.query(sql,[userCode],(err,data)=>{
            if(err){
                console.log('sql执行失败！');
                return;
            }
            
            res.send({"result":"ok"});
        })
        //释放连接
        conn.release();
    })
});

module.exports = router;
